package com.kui.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.kui.entity.Wenxian;
import com.kui.util.DBHelp;
import com.kui.util.RowMapper;

public class WenxianDao {
	
	DBHelp<Wenxian> db = new DBHelp<Wenxian>();
	
	private class WenxianRowMapper implements RowMapper<Wenxian>{

		public Wenxian mapperRow(ResultSet rs) throws SQLException {
			Wenxian w = new Wenxian();
			w.setId(rs.getString("id"));
			w.setName(rs.getString("name"));
			
			w.setAuthor(rs.getString("author"));
			w.setTime(rs.getString("time"));
			w.setDesc(rs.getString("desc"));
			w.setCreatetime(rs.getString("createtime"));
			w.setDownname(rs.getString("downname"));
			w.setSize(rs.getInt("size"));
			w.setType(rs.getString("type"));
			w.setT_qikan_id(rs.getString("t_qikan_id"));
			w.setT_type_id(rs.getString("t_type_id"));
			w.setType_name(rs.getString("type_name"));
			w.setQikan_name(rs.getString("qikan_name"));
			w.setKeyword(rs.getString("keyword"));
			return w;
		}
		
		
	}

	public List<Wenxian> findAll() {
		StringBuilder b = new StringBuilder();
		b.append("SELECT t_wenxian.id,t_wenxian.name,t_wenxian.author,t_wenxian.time,t_wenxian.desc,t_wenxian.createtime,t_wenxian.downname,t_wenxian.size,t_wenxian.type,t_wenxian.t_qikan_id,t_wenxian.t_type_id,t_wenxian.keyword,t_type.name AS type_name,t_qikan.name AS qikan_name");
		b.append(" FROM t_wenxian LEFT JOIN t_type");
		b.append(" ON (t_wenxian.t_type_id = t_type.id AND t_wenxian.t_qikan_id = t_type.t_qikan_id)");
		b.append(" LEFT JOIN t_qikan");
		b.append(" ON t_wenxian.t_qikan_id = t_qikan.id ORDER BY t_wenxian.createtime DESC");
		String sql = b.toString();
		return db.executeQueryForList(sql, new WenxianRowMapper());
	}

	public void save(Wenxian w) {
		String sql = "INSERT INTO t_wenxian(id,name,author,time,`desc`,createtime,downname,size,type,t_qikan_id,t_type_id,t_wenxian.keyword) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
		db.executeSQL(sql, w.getId(),w.getName(),w.getAuthor(),w.getTime(),w.getDesc(),w.getCreatetime(),w.getDownname(),w.getSize(),w.getType(),w.getT_qikan_id(),w.getT_type_id(),w.getKeyword());
		
	}

	public Wenxian findById(String wid) {
		StringBuilder b = new StringBuilder();
		b.append("SELECT t_wenxian.id,t_wenxian.name,t_wenxian.author,t_wenxian.time,t_wenxian.desc,t_wenxian.createtime,t_wenxian.downname,t_wenxian.size,t_wenxian.type,t_wenxian.t_qikan_id,t_wenxian.t_type_id,t_wenxian.keyword,t_type.name AS type_name,t_qikan.name AS qikan_name");
		b.append(" FROM t_wenxian LEFT JOIN t_type");
		b.append(" ON (t_wenxian.t_type_id = t_type.id AND t_wenxian.t_qikan_id = t_type.t_qikan_id)");
		b.append(" LEFT JOIN t_qikan");
		b.append(" ON t_wenxian.t_qikan_id = t_qikan.id");
		b.append(" WHERE t_wenxian.id = ?");
		String sql = b.toString();
		
		return db.executeQueryForObject(sql, new WenxianRowMapper(), wid);
	}

	public void update(Wenxian w, String wid) {
		String sql = "UPDATE t_wenxian SET id=?,name=?,author=?,time=?,`desc`=?,createtime=?,downname=?,size=?,type=?,t_qikan_id=?,t_type_id=?,keyword=? WHERE id=?";
		db.executeSQL(sql, w.getId(),w.getName(),w.getAuthor(),w.getTime(),w.getDesc(),w.getCreatetime(),w.getDownname(),w.getSize(),w.getType(),w.getT_qikan_id(),w.getT_type_id(),w.getKeyword(),wid);
	}

	public void del(String wid) {
		String sql = "DELETE FROM t_wenxian WHERE id = ?";
		db.executeSQL(sql, wid);
		
	}

	public List<Wenxian> findByQikanId(String qid) {
		StringBuilder b = new StringBuilder();
		b.append("SELECT t_wenxian.id,t_wenxian.name,t_wenxian.author,t_wenxian.time,t_wenxian.desc,t_wenxian.createtime,t_wenxian.downname,t_wenxian.size,t_wenxian.type,t_wenxian.t_qikan_id,t_wenxian.t_type_id,t_wenxian.keyword,t_type.name AS type_name,t_qikan.name AS qikan_name");
		b.append(" FROM t_wenxian LEFT JOIN t_type");
		b.append(" ON (t_wenxian.t_type_id = t_type.id AND t_wenxian.t_qikan_id = t_type.t_qikan_id)");
		b.append(" LEFT JOIN t_qikan");
		b.append(" ON t_wenxian.t_qikan_id = t_qikan.id");
		b.append(" WHERE t_wenxian.t_qikan_id = ? ORDER BY t_wenxian.createtime DESC");
		String sql = b.toString();
		return db.executeQueryForList(sql, new WenxianRowMapper(),qid);
	}

	public List<Wenxian> findByTypeId(String tid) {
		StringBuilder b = new StringBuilder();
		b.append("SELECT t_wenxian.keyword, t_wenxian.id,t_wenxian.name,t_wenxian.author,t_wenxian.time,t_wenxian.desc,t_wenxian.createtime,t_wenxian.downname,t_wenxian.size,t_wenxian.type,t_wenxian.t_qikan_id,t_wenxian.t_type_id,t_type.name AS type_name,t_qikan.name AS qikan_name");
		b.append(" FROM t_wenxian LEFT JOIN t_type");
		b.append(" ON (t_wenxian.t_type_id = t_type.id AND t_wenxian.t_qikan_id = t_type.t_qikan_id)");
		b.append(" LEFT JOIN t_qikan");
		b.append(" ON t_wenxian.t_qikan_id = t_qikan.id");
		b.append(" WHERE t_wenxian.t_type_id = ? ORDER BY t_wenxian.createtime DESC");
		String sql = b.toString();
		return db.executeQueryForList(sql, new WenxianRowMapper(),tid);
	}

	public Wenxian findWenxianByDname(String dname) {
		StringBuilder b = new StringBuilder();
		b.append("SELECT t_wenxian.keyword, t_wenxian.id,t_wenxian.name,t_wenxian.author,t_wenxian.time,t_wenxian.desc,t_wenxian.createtime,t_wenxian.downname,t_wenxian.size,t_wenxian.type,t_wenxian.t_qikan_id,t_wenxian.t_type_id,t_type.name AS type_name,t_qikan.name AS qikan_name");
		b.append(" FROM t_wenxian LEFT JOIN t_type");
		b.append(" ON (t_wenxian.t_type_id = t_type.id AND t_wenxian.t_qikan_id = t_type.t_qikan_id)");
		b.append(" LEFT JOIN t_qikan");
		b.append(" ON t_wenxian.t_qikan_id = t_qikan.id");
		b.append(" WHERE t_wenxian.downname = ?");
		String sql = b.toString();
		
		return db.executeQueryForObject(sql, new WenxianRowMapper(), dname);
	}

	public List<Wenxian> findLikeName(String data) {
		StringBuilder b = new StringBuilder();
		b.append("SELECT t_wenxian.keyword,t_wenxian.id,t_wenxian.name,t_wenxian.author,t_wenxian.time,t_wenxian.desc,t_wenxian.createtime,t_wenxian.downname,t_wenxian.size,t_wenxian.type,t_wenxian.t_qikan_id,t_wenxian.t_type_id,t_type.name AS type_name,t_qikan.name AS qikan_name");
		b.append(" FROM t_wenxian LEFT JOIN t_type");
		b.append(" ON (t_wenxian.t_type_id = t_type.id AND t_wenxian.t_qikan_id = t_type.t_qikan_id)");
		b.append(" LEFT JOIN t_qikan");
		b.append(" ON t_wenxian.t_qikan_id = t_qikan.id");
		b.append(" WHERE t_wenxian.name LIKE ? OR t_wenxian.keyword LIKE ? ORDER BY t_wenxian.createtime DESC");
		String sql = b.toString();
		return db.executeQueryForList(sql, new WenxianRowMapper(),"%"+data+"%","%"+data+"%");
	}

	public List<Wenxian> findByLike(String data) {
		StringBuilder b = new StringBuilder();
		b.append("SELECT t_wenxian.keyword,t_wenxian.id,t_wenxian.name,t_wenxian.author,t_wenxian.time,t_wenxian.desc,t_wenxian.createtime,t_wenxian.downname,t_wenxian.size,t_wenxian.type,t_wenxian.t_qikan_id,t_wenxian.t_type_id,t_type.name AS type_name,t_qikan.name AS qikan_name");
		b.append(" FROM t_wenxian LEFT JOIN t_type");
		b.append(" ON (t_wenxian.t_type_id = t_type.id AND t_wenxian.t_qikan_id = t_type.t_qikan_id)");
		b.append(" LEFT JOIN t_qikan");
		b.append(" ON t_wenxian.t_qikan_id = t_qikan.id");
		b.append(" WHERE t_wenxian.author LIKE ? ORDER BY t_wenxian.createtime DESC");
		String sql = b.toString();
		return db.executeQueryForList(sql, new WenxianRowMapper(),"%"+data+"%");
	}

	public List<Wenxian> findLikeQikan(String data) {
		StringBuilder b = new StringBuilder();
		b.append("SELECT t_wenxian.keyword,t_wenxian.id,t_wenxian.name,t_wenxian.author,t_wenxian.time,t_wenxian.desc,t_wenxian.createtime,t_wenxian.downname,t_wenxian.size,t_wenxian.type,t_wenxian.t_qikan_id,t_wenxian.t_type_id,t_type.name AS type_name,t_qikan.name AS qikan_name");
		b.append(" FROM t_wenxian LEFT JOIN t_type");
		b.append(" ON (t_wenxian.t_type_id = t_type.id AND t_wenxian.t_qikan_id = t_type.t_qikan_id)");
		b.append(" LEFT JOIN t_qikan");
		b.append(" ON t_wenxian.t_qikan_id = t_qikan.id");
		b.append(" WHERE t_qikan.name LIKE ? ORDER BY t_wenxian.createtime DESC");
		String sql = b.toString();
		return db.executeQueryForList(sql, new WenxianRowMapper(),"%"+data+"%");
	}

	public List<Wenxian> findLikeType(String data) {
		StringBuilder b = new StringBuilder();
		b.append("SELECT t_wenxian.keyword,t_wenxian.id,t_wenxian.name,t_wenxian.author,t_wenxian.time,t_wenxian.desc,t_wenxian.createtime,t_wenxian.downname,t_wenxian.size,t_wenxian.type,t_wenxian.t_qikan_id,t_wenxian.t_type_id,t_type.name AS type_name,t_qikan.name AS qikan_name");
		b.append(" FROM t_wenxian LEFT JOIN t_type");
		b.append(" ON (t_wenxian.t_type_id = t_type.id AND t_wenxian.t_qikan_id = t_type.t_qikan_id)");
		b.append(" LEFT JOIN t_qikan");
		b.append(" ON t_wenxian.t_qikan_id = t_qikan.id");
		b.append(" WHERE t_type.name LIKE ? ORDER BY t_wenxian.createtime DESC");
		String sql = b.toString();
		return db.executeQueryForList(sql, new WenxianRowMapper(),"%"+data+"%");
	}

	public List<Wenxian> findLikeId(String data) {
		StringBuilder b = new StringBuilder();
		b.append("SELECT t_wenxian.keyword,t_wenxian.id,t_wenxian.name,t_wenxian.author,t_wenxian.time,t_wenxian.desc,t_wenxian.createtime,t_wenxian.downname,t_wenxian.size,t_wenxian.type,t_wenxian.t_qikan_id,t_wenxian.t_type_id,t_type.name AS type_name,t_qikan.name AS qikan_name");
		b.append(" FROM t_wenxian LEFT JOIN t_type");
		b.append(" ON (t_wenxian.t_type_id = t_type.id AND t_wenxian.t_qikan_id = t_type.t_qikan_id)");
		b.append(" LEFT JOIN t_qikan");
		b.append(" ON t_wenxian.t_qikan_id = t_qikan.id");
		b.append(" WHERE t_wenxian.id LIKE ? ORDER BY t_wenxian.createtime DESC");
		String sql = b.toString();
		return db.executeQueryForList(sql, new WenxianRowMapper(),"%"+data+"%");
	}

	public List<Wenxian> findLike(String sb) {
		StringBuilder b = new StringBuilder();
		b.append("SELECT t_wenxian.keyword,t_wenxian.id,t_wenxian.name,t_wenxian.author,t_wenxian.time,t_wenxian.desc,t_wenxian.createtime,t_wenxian.downname,t_wenxian.size,t_wenxian.type,t_wenxian.t_qikan_id,t_wenxian.t_type_id,t_type.name AS type_name,t_qikan.name AS qikan_name");
		b.append(" FROM t_wenxian LEFT JOIN t_type");
		b.append(" ON (t_wenxian.t_type_id = t_type.id AND t_wenxian.t_qikan_id = t_type.t_qikan_id)");
		b.append(" LEFT JOIN t_qikan");
		b.append(" ON t_wenxian.t_qikan_id = t_qikan.id");
		b.append(" WHERE ");
		b.append(sb);
		b.append(" ORDER BY t_wenxian.createtime DESC");
		String sql = b.toString();
		return db.executeQueryForList(sql, new WenxianRowMapper());
	}
	

}
